package top.withwings.bonus.bonus.repo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import top.withwings.bonus.bonus.pojo.BonusForm;
import top.withwings.bonus.bonus.pojo.Good;
import top.withwings.bonus.bonus.pojo.Rate;
import top.withwings.bonus.common.pojo.PageParam;
import top.withwings.bonus.common.pojo.ReinforcedBeanPropertySqlParameterSource;
import top.withwings.bonus.common.pojo.User;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository
public class BonusFormRepo{

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public int create(BonusForm bonusForm) {
        String sql =
                " insert into bonus_form ( "
                        + " salesman_id, "
                        + " effective_date, "
                        + " good_id, "
                        + " batch_no, "
                        + " expiration, "
                        + " price, "
                        + " quantity, "
                        + " amount, "
                        + " make_up, "
                        + " remark) "

                        + " values( "

                        + " :salesmanId, "
                        + " :effectiveDate, "
                        + " :goodId, "
                        + " :batchNo, "
                        + " :expiration, "
                        + " :price, "
                        + " :quantity, "
                        + " :amount, "
                        + " :makeUp, "
                        + " :remark "
                        + " ) ";


        Map<String, Object> params = new HashMap<>();
        params.put("salesmanId", bonusForm.getSalesman().getId());
        params.put("goodId", bonusForm.getGood().getId());

        SqlParameterSource sqlParameterSource = new ReinforcedBeanPropertySqlParameterSource(bonusForm, params);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, sqlParameterSource, keyHolder);
        return keyHolder.getKey().intValue();

    }

    public void delete(int bonusFormId) {
        String sql = "delete from bonus_form where id = ?";
        jdbcTemplate.update(sql, bonusFormId);

    }

    public void update(BonusForm bonusForm) {
        String sql = " update  "
                + " bonus_form "
                + " set "
                + " salesman_id = :salesmanId, "
                + " effective_date = :effectiveDate, "
                + " good_id = :goodId, "
                + " batch_no = :batchNo, "
                + " expiration = :expiration, "
                + " price = :price, "
                + " quantity = :quantity, "
                + " amount = :amount, "
                + " make_up = :makeUp, "
                + " remark = :remark, "
                + " final_rate_type = :finalRateType, "
                + " final_rate_value = :finalRateValue, "
                + " computed_date = :computedDate, "
                + " single_rate1_type = :singleRate1Type, "
                + " single_rate1_value = :singleRate1Value, "
                + " single_rate2_type = :singleRate2Type, "
                + " single_rate2_value = :singleRate2Value, "
                + " single_rate3_type = :singleRate3Type, "
                + " single_rate3_value = :singleRate3Value "
                + " where id = :id ";
        Map<String, Object> params = new HashMap<>();
        params.put("salesmanId", bonusForm.getSalesman().getId());
        params.put("goodId", bonusForm.getGood().getId());

        params.put("finalRateType", bonusForm.getFinalRate().getType());
        params.put("finalRateValue", bonusForm.getFinalRate().getValue());

        params.put("singleRate1Type", bonusForm.getSingleRates().get(0).getType());
        params.put("singleRate1Value", bonusForm.getSingleRates().get(0).getValue());

        params.put("singleRate2Type", bonusForm.getSingleRates().get(1).getType());
        params.put("singleRate2Value", bonusForm.getSingleRates().get(1).getValue());

        params.put("singleRate3Type", bonusForm.getSingleRates().get(2).getType());
        params.put("singleRate3Value", bonusForm.getSingleRates().get(2).getValue());

        SqlParameterSource sqlParameterSource = new ReinforcedBeanPropertySqlParameterSource(bonusForm, params);

        namedParameterJdbcTemplate.update(sql, sqlParameterSource);


    }

    public BonusForm findById(int bonusFormId) {
        String sql = " select "
                + " b.id, "
                + " b.effective_date, "
                + " b.batch_no, "
                + " b.expiration, "
                + " b.price, "
                + " b.quantity, "
                + " b.amount, "
                + " b.make_up, "
                + " b.remark, "
                + " b.computed_date, "
                + " b.final_rate_type, "
                + " b.final_rate_value, "
                + " b.single_rate1_type, "
                + " b.single_rate1_Value, "
                + " b.single_rate2_type, "
                + " b.single_rate2_value, "
                + " b.single_rate3_type, "
                + " b.single_rate3_value, "
                + " b.good_id, "
                + " g.code as good_code, "
                + " g.name as good_name, "
                + " g.specification as good_specification, "
                + " g.standard_price as good_standard_price, "
                + " b.salesman_id, "
                + " s.realname as salesman_name "
                + " from  "
                + " bonus_form b "
                + " inner join  "
                + " good g on b.good_id = g.id "
                + " inner join  "
                + " user s on b.salesman_id = s.id "
                + " where b.id = ?";

        try {
            return jdbcTemplate.queryForObject(sql, new BonusFormRowMapper(), bonusFormId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    public List<BonusForm> search(PageParam pageParam ,Integer salesmanId, Date startTime, Date endTime) {

        String sql =" select "
                + " b.id, "
                + " b.effective_date, "
                + " b.batch_no, "
                + " b.expiration, "
                + " b.price, "
                + " b.quantity, "
                + " b.amount, "
                + " b.make_up, "
                + " b.remark, "
                + " b.computed_date, "
                + " b.final_rate_type, "
                + " b.final_rate_value, "
                + " b.single_rate1_type, "
                + " b.single_rate1_Value, "
                + " b.single_rate2_type, "
                + " b.single_rate2_value, "
                + " b.single_rate3_type, "
                + " b.single_rate3_value, "
                + " b.good_id, "
                + " g.code as good_code, "
                + " g.name as good_name, "
                + " g.specification as good_specification, "
                + " g.standard_price as good_standard_price, "
                + " b.salesman_id, "
                + " s.realname as salesman_name "
                + " from  "
                + " bonus_form b "
                + " inner join  "
                + " good g on b.good_id = g.id "
                + " inner join  "
                + " user s on b.salesman_id = s.id "
                + " where b.effective_date between :startTime and :endTime ";
        if (salesmanId != null) {
            sql += " and b.salesman_id = :salesmanId";
        }

        sql += " limit :start, :length";

        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("salesmanId", salesmanId);
        paramMap.put("startTime", startTime);
        paramMap.put("endTime", endTime);
        paramMap.put("start", pageParam.getStart());
        paramMap.put("length", pageParam.getLength());

        return namedParameterJdbcTemplate.query(sql, paramMap, new BonusFormRowMapper());
    }

    public int count(Integer salesmanId, Date startTime, Date endTime) {

        if (salesmanId != null) {
            String sql = "select count(*) from bonus_form where salesman_id = ?  and effective_date between ? and ?";
            return jdbcTemplate.queryForObject(sql, Integer.class, salesmanId, startTime, endTime);
        } else {
            String sql = "select count(*) from bonus_form where effective_date between ? and ?";
            return jdbcTemplate.queryForObject(sql, Integer.class, startTime, endTime);
        }


    }


    private static class BonusFormRowMapper implements RowMapper<BonusForm> {

        private BeanPropertyRowMapper<BonusForm> beanPropertyRowMapper = new BeanPropertyRowMapper(BonusForm.class);

        @Override
        public BonusForm mapRow(ResultSet rs, int rowNum) throws SQLException {

            BonusForm form = beanPropertyRowMapper.mapRow(rs, rowNum);

            Rate finalRate = new Rate(rs.getInt("final_rate_type"), rs.getDouble("final_rate_value"));

            form.setFinalRate(finalRate);

            Rate singleRate1 = new Rate(rs.getInt("single_rate1_type"), rs.getDouble("single_rate1_value"));
            Rate singleRate2 = new Rate(rs.getInt("single_rate2_type"), rs.getDouble("single_rate2_value"));
            Rate singleRate3 = new Rate(rs.getInt("single_rate3_type"), rs.getDouble("single_rate3_value"));

            form.setSingleRates(Arrays.asList(singleRate1, singleRate2, singleRate3));

            User salesman = new User();
            salesman.setId(rs.getInt("salesman_id"));
            salesman.setRealname(rs.getString("salesman_name"));
            form.setSalesman(salesman);

            Good good = new Good();
            good.setId(rs.getInt("good_id"));
            good.setName(rs.getString("good_name"));
            good.setStandardPrice(rs.getDouble("good_standard_price"));
            good.setSpecification(rs.getString("good_specification"));
            good.setCode(rs.getString("good_code"));

            form.setGood(good);

            return form;
        }
    }
    public boolean hasBonusFormOfGood(int goodId){
        String sql="select id from bonus_form where good_id=? limit 1";
        return jdbcTemplate.queryForObject(sql,Integer.class,goodId) > 0;
    }

    public boolean hasBonusFormOfSalesman(int salesmanId){
        String sql="select id from bonus_form where salesman_id =? limit 1";
        int formCounts=jdbcTemplate.queryForObject(sql,Integer.class,salesmanId);
        return formCounts >0;
    }
}
